package Models;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.PreparedStatement;

import Models.Student;

public class JdbcUtils {
	
	private static Connection getConn() {
	    String driver = "com.mysql.jdbc.Driver";
	    String url = "jdbc:mysql://localhost:3306/test";
	    String username = "root";
	    String password = "";
	    Connection conn = null;
	    try {
	        Class.forName(driver); //classLoader,加载对应驱动
	        conn = (Connection) DriverManager.getConnection(url, username, password);
	    } catch (ClassNotFoundException e) {
	        e.printStackTrace();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return conn;
	}
	 private static int insert(Student student) {
		    Connection conn = getConn();
		    int i = 0;
		    String sql = "insert into students (id,Name,Password) values(?,S?,?)";
		    PreparedStatement pstmt;
		    try {
		        pstmt = (PreparedStatement) conn.prepareStatement(sql);
		        pstmt.setInt(3, student.getId());
		        pstmt.setString(1, student.getName());
		        pstmt.setString(2, student.getPassword());
		     
		        i = pstmt.executeUpdate();
		        pstmt.close();
		        conn.close();
		    } catch (SQLException e) {
		        e.printStackTrace();
		    }
		    return i;
		}
	 private static int update(Student student) {
		    Connection conn = getConn();
		    int i = 0;
		    String sql = "update students set Password='" + student.getPassword()+ "' where Name='" + student.getName() + "'";
		    PreparedStatement pstmt;
		    try {
		        pstmt = (PreparedStatement) conn.prepareStatement(sql);
		        i = pstmt.executeUpdate();
		        System.out.println("resutl: " + i);
		        pstmt.close();
		        conn.close();
		    } catch (SQLException e) {
		        e.printStackTrace();
		    }
		    return i;
		}
  private static Integer getAll() {
		    Connection conn = getConn();
		    String sql = "select * from students";
		    PreparedStatement pstmt;
		    try {
		        pstmt = (PreparedStatement)conn.prepareStatement(sql);
		        ResultSet rs = pstmt.executeQuery();
		        int col = rs.getMetaData().getColumnCount();
		        System.out.println("============================");
		        while (rs.next()) {
		            for (int i = 1; i <= col; i++) {
		                System.out.print(rs.getString(i) + "\t");
		                if ((i == 2) && (rs.getString(i).length() < 8)) {
		                    System.out.print("\t");
		                }
		             }
		            System.out.println("");
		        }
		            System.out.println("============================");
		    } catch (SQLException e) {
		        e.printStackTrace();
		    }
		    return null;
		}
	 private static int delete(String name) {
		    Connection conn = getConn();
		    int i = 0;
		    String sql = "delete from students where Name='" + name + "'";
		    PreparedStatement pstmt;
		    try {
		        pstmt = (PreparedStatement) conn.prepareStatement(sql);
		        i = pstmt.executeUpdate();
		        System.out.println("resutl: " + i);
		        pstmt.close();
		        conn.close();
		    } catch (SQLException e) {
		        e.printStackTrace();
		    }
		    return i;
		}
	 private static ResultSet findStudent(Student student) {
		    Connection conn = getConn();
		    String sql = "select * from students where Name="+student.getName();
		    PreparedStatement pstmt;
		    ResultSet rs = null;
		        try {
					pstmt = (PreparedStatement)conn.prepareStatement(sql);
					 rs = pstmt.executeQuery();
					   
					   pstmt.close();
				        conn.close();
				        
					      
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		        return rs;
		    
		}
}
